const express = require('express')
const router = express.Router()
const humps = require('humps')
const db = require('../../database/db')

const concatPromotionsSql = (promotions, str) => {

    if (promotions.length) {
        str += '('
        promotions.forEach((item, i) => {
            if (i < promotions.length - 1) {
                str += 'pc.`name` = "' + item + '" OR '
            } else {
                str += 'pc.`name` = "' + item + '") AND'
            }
        })
    }
    return str
}

const concatCenterDeptSql = (table, centerDept, type, str) => {

    if (centerDept.length) {
        centerDept.forEach((item, i) => {
            if (i < centerDept.length - 1) {
                str += '' + table + '.`' + type + '_id` = "' + item.id + '" OR '
            } else {
                str += '' + table + '.`' + type + '_id` = "' + item.id + '"'
            }
        })
    }
    return str
}
const getLastDay = (y, m) => {
    let dt = new Date(y, m, 1)
    cdt = new Date(dt.getTime() - 1000 * 60 * 60 * 24)
    return cdt.getDate()
}
const getPlanTime = (from, to) => {
    const from_y = from.split('-')[0]
    const to_y = to.split('-')[0]
    const from_m = from.split('-')[1]
    const to_m = to.split('-')[1]
    const res_to = `${to_y}-${to_m}-${getLastDay(to_y, +to_m)}`

    if (from_m === to_m) { // 同一个月的时候
        return { from: `${from_y}-${from_m}-01`, to: res_to }
    } else {
        return { from: `${from_y}-${from_m}-01`, to: res_to }
    }
}
module.exports = (() => {
    router.post('/Stats_completionRate1', async (req, res, next) => {
        try {
            let str = '',
                complete = [],
                plan = []

            const promises = [],
                depts = [],
                xAxis = [],
                resoult1 = [],
                resoult2 = [],
                resoult3 = []
            const filter_repeat = (plans, type) => {
                let obj = {}
                plans.forEach((item) => {
                    let key = '' + item.centerId + item.departmentId
                    if (obj[key]) {
                        obj[key][type] += item[type]
                    } else {
                        obj[key] = item
                    }
                })
                return Object.values(obj)
            }
            const extract = async (obj1, obj2, planType) => {
                const _plans = await filter_repeat(obj2, planType)
                obj1.forEach(item => {
                    complete.push(
                        `${item.amount}-${item.centerName}[${item.deptName}]`
                    )
                })

                _plans.forEach(item => {
                    plan.push(
                        `${item[planType]}-${item.centerName}[${item.deptName}]`
                    )
                })
                if (!complete.length && !plan.length) {
                    res.send({
                        xAxis,
                        resoult1,
                        resoult2,
                        resoult3,
                    })
                } // 计划和完成都没有
                if (complete.length && plan.length) {
                    // 都有
                    plan.forEach((item, i) => {
                        complete.forEach((ele, j) => {
                            if (
                                complete[j].split("-")[1] ===
                                plan[i].split("-")[1]
                            ) {
                                xAxis.push(complete[j].split("-")[1])
                                resoult1.push(
                                    (
                                        (complete[j].split("-")[0] /
                                            plan[i].split("-")[0]) *
                                        100
                                    ).toFixed(2)
                                )
                                resoult2.push(complete[j].split("-")[0])
                                resoult3.push(plan[i].split("-")[0])
                            }
                        })
                    })
                    res.send({
                        xAxis,
                        resoult1,
                        resoult2,
                        resoult3,
                    })
                }
            }
            const judge = async (str, plans) => {
                if (req.body.type === 'visitComplete') {
                    const visits = humps.camelizeKeys(await db.getStats_RateOfDept({ from: req.body.from, to: req.body.to, sql: str }))
                    extract(visits, plans, 'visitPlan')
                } else {
                    str = 'r.`is_sign` = 1 AND '
                    const signs = humps.camelizeKeys(await db.getStats_RateOfDept({ from: req.body.from, to: req.body.to, sql: str }))
                    extract(signs, plans, 'signPlan')
                }
            }
            if (req.body.depts.length) {
                req.body.depts.forEach(dept => {
                    const promise = new Promise(async resolve => {
                        depts.push(await db.getDeptByName({ name: dept.split('[')[1].split(']')[0], center: dept.split('[')[0] }))
                        resolve(depts)
                    })
                    promises.push(promise)
                })
                Promise.all(promises).then(async () => {
                    str += '('
                    const plans = humps.camelizeKeys(await db.getStats_plansOfDept({ from: getPlanTime(req.body.from, req.body.to).from, to: getPlanTime(req.body.from, req.body.to).to, sql: await concatCenterDeptSql('p', depts, 'department', str) + ') AND ' }))
                    str = await concatCenterDeptSql('r', depts, 'department', str)
                    str += ') AND '
                    judge(str, plans)
                })
            } else {
                judge(str)
            }
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_negotiator', async (req, res, next) => {
        try {
            let str = ''
            const xAxis = [],
                resoult1 = [],
                resoult2 = [],
                resoult3 = []

            const toObj = (arrs) => {
                let obj = {}
                arrs.forEach((item) => {
                    let key = item.negotiatorName
                    if (obj[key]) {
                        obj[key] += item.amount
                    } else {
                        obj[key] = item.amount
                    }
                })
                return obj
            }

            const negotiators = humps.camelizeKeys(await db.getNegotiators())
            const ordertakings = await toObj(humps.camelizeKeys(await db.getStats_negotiator({ from: req.body.from, to: req.body.to, sql: str })))
            str = 'r.`is_sign` = 1 AND '
            const completes = await toObj(humps.camelizeKeys(await db.getStats_negotiator({ from: req.body.from, to: req.body.to, sql: str })))

            negotiators.forEach(item => {
                const key = item.name
                const ordertaking = ordertakings[key] || 0
                const complete = completes[key] || 0
                resoult1.push(ordertaking)
                resoult2.push(complete)
                resoult3.push(((complete / ordertaking) * 100 || 0).toFixed())
                xAxis.push(key)
            })
            res.send({ xAxis, resoult1, resoult2, resoult3 })
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_completionRate', async (req, res, next) => {
        try {
            let str = '',
                complete = 0,
                plan = 0

            const judge = (obj1, type, plans) => {
                obj1.forEach(item => {
                    complete += +item.amount
                })
                plans.forEach(item => {
                    plan += +item[`${type}Plan`]
                })
                res.send({
                    value:
                        plans.length && obj1.length
                            ? ((complete / plan) * 100).toFixed(2)
                            : 0,
                    name: req.body.center
                })
            }
            if (req.body.type === 'visitComplete') {
                const visits = humps.camelizeKeys(await db.getStats_RateOfCenter({ from: req.body.from, to: req.body.to, sql: str, center: req.body.center }))
                const plans = humps.camelizeKeys(await db.getStats_plansOfCenter({ from: getPlanTime(req.body.from, req.body.to).from, to: getPlanTime(req.body.from, req.body.to).to, center: req.body.center }))
                judge(visits, 'visit', plans)
            } else {
                str = 'r.`is_sign` = 1 AND '
                const signs = humps.camelizeKeys(await db.getStats_RateOfCenter({ from: req.body.from, to: req.body.to, sql: str, center: req.body.center }))
                const plans = humps.camelizeKeys(await db.getStats_plansOfCenter({ from: getPlanTime(req.body.from, req.body.to).from, to: getPlanTime(req.body.from, req.body.to).to, center: req.body.center }))
                judge(signs, 'sign', plans)
            }
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_buyType', async (req, res, next) => {
        try {
            const mortgage = humps.camelizeKeys(await db.getStats_buyType({ year: req.body.time, group: 'r.`is_mortgage`', sql: '' }))
            const [mfrFinance] = humps.camelizeKeys(await db.getStats_buyType({ year: req.body.time, group: 'r.`mfr_finance`', sql: 'AND r.`mfr_finance` = 1 ' }))
            const resoult = []
            mortgage.concat(mfrFinance).forEach((item, i) => {
                let name = ''
                name = item.isMortgage ? '按揭' : item.mfrFinance ? '厂家金融' : '全款'
                resoult.push({
                    value: item.amount,
                    name
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_carType', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_carType(req.body.time))
            const resoult = []
            doc.forEach((item, i) => {
                resoult.push({
                    value: item.amount,
                    name: item.cartype
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_warrantManRank', async (req, res, next) => {
        try {
            let str = ''
            const resoult = {
                warrantMan: [],
                amount: [],
                name: ''
            }
            const judge = (data, name = '数量', type = 'amount') => {
                data.reverse().forEach(item => {
                    resoult.warrantMan.push(item.warrantmanName);
                    resoult.amount.push(item[type]);
                    resoult.name = name;
                });
                return resoult
            }
            const query = async (str, time) => {
                return humps.camelizeKeys(await db.getStats_warrantManRank({ from: req.body.from, to: req.body.to, sql: str, time }))
            }
            switch (req.body.type) {
                case 'ordertaking':
                    str = 'r.`is_sign` = 1 AND r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND '
                    res.send(judge(await query(str, 'time')))
                    break;
                case 'pass':
                    str = 'r.`is_sign` = 1 AND r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND r.`trial_id` != 0 AND '
                    res.send(judge(await query(str, 'pass_time')))
                    break;
                default:
                    str = 'r.`is_sign` = 1 AND r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND r.`trial_id` != 0 AND r.`is_pick_car` = 1 AND '
                    res.send(judge(await query(str, 'pick_car_time')))
            }
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_salesmanRank', async (req, res, next) => {
        try {
            let str = ''
            const resoult = {
                salesmanName: [],
                amount: [],
                name: ''
            }
            const judge = (data, name = '数量', type = 'amount') => {
                data.reverse().forEach(item => {
                    resoult.salesmanName.push(item.salesmanName);
                    resoult.amount.push(item[type]);
                    resoult.name = name;
                });
                return resoult
            }
            const query = async (str = '', isCharge = 0, time = 'time') => {
                return humps.camelizeKeys(isCharge ? await db.getStats_salesmanRankCharge({ from: req.body.from, to: req.body.to, sql: str }) :
                    await db.getStats_salesmanRank({ from: req.body.from, to: req.body.to, sql: str, time }))
            }
            switch (req.body.type) {
                case 'visit':
                    res.send(judge(await query()))
                    break;
                case 'sign':
                    str = 'r.`is_sign` = 1 AND '
                    res.send(judge(await query(str)))
                    break;
                case 'pickCar':
                    str = 'r.`is_sign` = 1 AND r.`is_pick_car` = 1 AND '
                    res.send(judge(await query(str, 0, 'pick_car_time')))
                    break;
                default:
                    str = 'r.`is_sign` = 1 AND r.`charge` != 0 AND '
                    res.send(judge(await query(str, 1), '金额', 'sum'))
            }
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_ProContrast', async (req, res, next) => {
        try {
            let str = '',
                sql = '',
                doc = null,
                name = ''
            const xAxis = [],
                sum = []
            const query = async (sql, type = 'records', name = '数量', value = 'amount', time = 'time') => {
                switch (type) {
                    case 'amount':
                        doc = humps.camelizeKeys(await db.getStats_packagesFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id' }))
                        break
                    case 'charge':
                        doc = humps.camelizeKeys(await db.getStats_ChargeFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id' }))
                        break
                    case 'cost':
                        doc = humps.camelizeKeys(await db.getStats_CostFilter({ from: req.body.from, to: req.body.to, sql }))
                        break
                    default:
                        doc = humps.camelizeKeys(await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id', time }))
                }
                doc.forEach(item => {
                    xAxis.push(item.promotionName)
                    sum.push(item[value])
                    name
                })
                return { xAxis, sum, name }
            }
            switch (req.body.type) {
                case 'amount':
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    res.send(await query(sql, 'amount', '数量', 'sum'))
                    break
                case 'visit':
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    res.send(await query(sql, 'records', '数量', 'amount', 'time'))
                    break
                case 'sign':
                    str = 'r.`is_sign` = 1 AND '
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    res.send(await query(sql, 'records', '数量', 'amount', 'time'))
                    break
                case 'pickCar':
                    str = 'r.`is_sign` = 1 AND r.`is_pick_car` = 1 AND '
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    res.send(await query(sql, 'records', '数量', 'amount', 'pick_car_time'))
                    break
                case 'charge':
                    str = 'r.`is_sign` = 1 AND r.`charge` != 0 AND '
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    res.send(await query(sql, 'charge', '费用', 'sum'))
                    break
                case 'cost':
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    res.send(await query(sql, 'cost', '费用', 'sum'))
                    break
                default:
                    sql = await concatPromotionsSql(req.body.promotions, str)
                    doc = humps.camelizeKeys(await db.getStats_CostFilter({ from: req.body.from, to: req.body.to, sql }))
                    doc.forEach(item => {
                        if (item.amount) {
                            xAxis.push(item.promotionName)
                            sum.push((item.sum / item.amount).toFixed())
                            name = "成本"
                        }
                    })
                    res.send({ xAxis, sum, name })
            }
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_ProTransform', async (req, res, next) => {
        try {
            let str = '',
                str1 = ''
            const center = await db.getCenterByName(req.body.center)
            const dept = await db.getDeptByName({ name: req.body.dept, center: req.body.center })
            if (req.body.center) {
                str1 = 'p.`center_id` = ' + center.id + ' AND '
            }

            if (req.body.dept) {
                str1 = 'p.`department_id` = ' + dept.id + ' AND '
            }

            const _package = await db.getStats_packagesFilter({ from: req.body.from, to: req.body.to, sql: await concatPromotionsSql(req.body.promotions, str1) })

            if (req.body.center) {
                str = 'r.`center_id` = ' + center.id + ' AND '
            }

            if (req.body.dept) {
                str = 'r.`department_id` = ' + dept.id + ' AND '
            }

            let sql = await concatPromotionsSql(req.body.promotions, str)
            const visit = await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id', time: 'time' })

            str += 'r.`is_sign` = 1 AND '
            sql = await concatPromotionsSql(req.body.promotions, str)
            const sign = await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id', time: 'time' })

            str += 'r.`is_ordertaking` = 1 AND '
            sql = await concatPromotionsSql(req.body.promotions, str)
            const enter = await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id', time: 'time' })

            str += 'r.`is_pick_car` = 1 AND '
            sql = await concatPromotionsSql(req.body.promotions, str)
            const pickCar = await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group: 'promotion_id', time: 'pick_car_time' })
            let packageSum = 0,
                visitSum = 0,
                signSum = 0,
                enterSum = 0,
                pickCarSum = 0
            _package.forEach(item => {
                packageSum += +item.sum
            })
            visit.forEach(item => {
                visitSum += +item.amount
            })
            sign.forEach(item => {
                signSum += +item.amount
            })
            enter.forEach(item => {
                enterSum += +item.amount
            })
            pickCar.forEach(item => {
                pickCarSum += +item.amount
            })
            res.send({
                packageSum,
                visitSum,
                signSum,
                enterSum,
                pickCarSum
            })
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_WarrTransform', async (req, res, next) => {
        try {
            let str = ''
            if (req.body.trial) {
                str += 'tc.`name` = "' + req.body.trial + '" AND '
            }

            if (req.body.warrantMan) {
                str += 'wm.`name` = "' + req.body.warrantMan + '" AND '
            }

            const ordertaking = await db.getStats_WarrOrdertaking({ from: req.body.from, to: req.body.to, sql: str })

            str += 'r.`trial_id` != 0 AND '
            const pass = await db.getStats_WarrTransform({ from: req.body.from, to: req.body.to, sql: str })

            str += 'r.`loan_amount` != 0 AND '
            const loanAmount = await db.getStats_WarrTransform({ from: req.body.from, to: req.body.to, sql: str })

            str += 'r.`is_pick_car` = 1 AND '
            const pickCar = await db.getStats_WarrPicks({ from: req.body.from, to: req.body.to, sql: str })
            let ordertakingSum = 0,
                passSum = 0,
                loanAmountSum = 0,
                pickCarSum = 0
            ordertaking.forEach(item => {
                ordertakingSum += +item.amount;
            });
            pass.forEach(item => {
                passSum += +item.amount;
            });
            loanAmount.forEach(item => {
                loanAmountSum += +item.amount;
            });
            pickCar.forEach(item => {
                pickCarSum += +item.amount;
            });
            res.send({
                ordertakingSum,
                passSum,
                loanAmountSum,
                pickCarSum
            })
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_CenterDept', async (req, res, next) => {
        try {
            let str = '',
                sql = '',
                group = ''
            const centers = [],
                depts = [],
                xAxis = [],
                resoult = []
            const promiseAll = (datas) => {
                const promises = []
                datas.forEach(data => {
                    const promise = new Promise(async resolve => {
                        const dept = await db.getDeptById(data.departmentId)
                        data.deptName = dept.name
                        const center = await db.getCenterById(data.centerId)
                        data.centerName = center.name
                        resolve(data)
                    })
                    promises.push(promise)
                })
                return Promise.all(promises)
            }

            const extract = (obj, name = '数量', type = 'amount') => {
                promiseAll(obj).then(() => {
                    obj.forEach(item => {
                        if (
                            (req.body.centers.length &&
                                !req.body.depts.length) ||
                            (!req.body.centers.length &&
                                !req.body.depts.length)
                        ) {
                            xAxis.push(item.centerName)
                        } else {
                            xAxis.push(
                                `${item.centerName}${item.deptName}`
                            )
                        }
                        resoult.push(item[type])
                    })
                    res.send({
                        xAxis,
                        resoult,
                        name
                    })
                })
            }
            const judge = async (str) => {
                req.body.depts.length ? group = 'department_id' : group = 'center_id'
                switch (req.body.type) {
                    case 'visit':
                        sql = await concatPromotionsSql(req.body.promotions, str)
                        const visits = humps.camelizeKeys(await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group, time: 'time' }))
                        extract(visits)
                        break
                    case 'sign':
                        str += 'r.`is_sign` = 1 AND '
                        sql = await concatPromotionsSql(req.body.promotions, str)
                        const signs = humps.camelizeKeys(await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group, time: 'time' }))
                        extract(signs)
                        break
                    case 'pickCar':
                        str += 'r.`is_pick_car` = 1 AND '
                        sql = await concatPromotionsSql(req.body.promotions, str)
                        const pickCars = humps.camelizeKeys(await db.getStats_recordsFilter({ from: req.body.from, to: req.body.to, sql, group, time: 'pick_car_time' }))
                        extract(pickCars)
                        break
                    default:
                        str += 'r.`charge` != 0 AND '
                        sql = await concatPromotionsSql(req.body.promotions, str)
                        const charges = humps.camelizeKeys(await db.getStats_ChargeFilter({ from: req.body.from, to: req.body.to, sql, group: 'department_id' }))
                        extract(charges, '费用', 'sum')
                        break
                }
            }
            if (req.body.centers.length) { // 有中心筛选的时候
                const promises = []
                req.body.centers.forEach(center => {
                    const promise = new Promise(async resolve => {
                        centers.push(await db.getCenterByName(center))
                        resolve(centers)
                    })
                    promises.push(promise)
                })
                Promise.all(promises).then(async () => {
                    str += await concatCenterDeptSql('r', centers, 'center', str + '(')
                    if (req.body.depts.length) { // 有部门筛选的时候
                        const promises1 = []
                        req.body.depts.forEach(dept => {
                            const promise1 = new Promise(async resolve => {
                                depts.push(await db.getDeptByName({ name: dept.split('[')[1].split(']')[0], center: dept.split('[')[0] }))
                                resolve(depts)
                            })
                            promises1.push(promise1)
                        })
                        Promise.all(promises1).then(async () => {
                            str += ' OR '
                            str = await concatCenterDeptSql('r', depts, 'department', str)
                            str += ') AND '
                            judge(str)
                        })
                    } else {
                        str += ') AND '
                        judge(str)
                    }
                })
            } else if (!req.body.centers.length && req.body.depts.length) { // 没有中心筛选有部门筛选的时候
                const promises1 = []
                req.body.depts.forEach(dept => {
                    const promise1 = new Promise(async resolve => {
                        depts.push(await db.getDeptByName({ name: dept.split('[')[1].split(']')[0], center: dept.split('[')[0] }))
                        resolve(depts)
                    })
                    promises1.push(promise1)
                })
                Promise.all(promises1).then(async () => {
                    str = await concatCenterDeptSql('r', depts, 'department', str + '(')
                    str += ') AND '
                    judge(str)
                })
            } else {
                judge(str)
            }
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_yearOperate', async (req, res, next) => {
        try {
            let str = ''
            const filter = (data, type) => {
                const obj = {}
                data.forEach(item => {
                    item.m = item.m < 10 ? `0${item.m}` : `${item.m}`
                    if (obj[item.m]) {
                        obj[item.m] += +item[type]
                    } else {
                        obj[item.m] = +item[type]
                    }
                })
                return obj
            }
            const judge = async (sum = '', type = 'amount', group = "time") => {
                const result = []
                const doc = filter(await db.getStats_yearOperate({ year: req.body.time, sql: str, sum, group }), type)
                for (var i = 1; i < 12; i++) {
                    result.push(doc[i < 10 ? `0${i}` : `${i}`] || 0)
                }
                res.send(result)
            }
            switch (req.body.type) {
                case 'visit':
                    judge()
                    break;
                case 'ordertaking':
                    str += 'r.`is_ordertaking` = 1 AND'
                    judge()
                    break;
                case 'sign':
                    str += 'r.`is_sign` = 1 AND'
                    judge()
                    break;
                case 'pass':
                    str += 'r.`pass_time` != 0 AND'
                    judge('', 'amount', 'pass_time')
                    break;
                case 'pickCar':
                    str += 'r.`is_pick_car` = 1 AND'
                    judge('', 'amount', 'pick_car_time')
                    break;
                case 'charge':
                    str += 'r.`charge` != 0 AND'
                    judge('', 'amount', 'pick_car_time')
                    break;
                case 'chargeAmount':
                    str += 'r.`charge` != 0 AND'
                    judge('sum(r.`charge`) charge,', 'charge', 'pick_car_time')
                    break;
                case 'achievement':
                    str += 'r.`achievement` != 0 AND'
                    judge('sum(r.`achievement`) achievement,', 'achievement')
                    break;
                default:
                    const distribute = filter(await db.getYearOperateDistr(req.body.time), 'sum')
                    const result = []
                    for (var i = 1; i < 12; i++) {
                        result.push(distribute[i < 10 ? `0${i}` : `${i}`] || 0)
                    }
                    res.send(result)
            }
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_pakages', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_pakages({ from: req.query.from, to: req.query.to }))
            let total = [],
                totalSum = 0
            const day = []
            doc.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                total.push(item.total)
                totalSum += +item.total
            })
            res.send({ day, total, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_visits', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_visits({ from: req.query.from, to: req.query.to }))
            let amount = [],
                totalSum = 0
            const day = []
            doc.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_signs', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_signs({ from: req.query.from, to: req.query.to }))
            let amount = [],
                totalSum = 0
            const day = []
            doc.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_warrantOrdertakings', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_warrantOrdertakings({ from: req.query.from, to: req.query.to }))
            let amount = [],
                totalSum = 0
            const day = []
            doc.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_resourceOrdertakings', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_resourceOrdertakings({ from: req.query.from, to: req.query.to }))
            let amount = [],
                totalSum = 0
            const day = []
            doc.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_pickCars', async (req, res, next) => {
        try {
            let str = '',
                amount = [],
                totalSum = 0
            const day = []
            if (req.query.type === 'resoures') {
                str = 'AND r.`is_mortgage` = 0'
            }

            if (req.query.type === 'warrant') {
                str = 'AND r.`is_mortgage` = 1'
            }

            const doc = humps.camelizeKeys(await db.getStats_pickCars({ from: req.query.from, to: req.query.to, sql: str }))
            doc.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_finances', async (req, res, next) => {
        try {
            const charge = await db.getStats_charges({ from: req.query.from, to: req.query.to })
            const market_charge = await db.getStats_marketCharges({ from: req.query.from, to: req.query.to })
            let result1 = 0,
                result2 = 0
            charge.forEach(item => {
                result1 += item.charge
            })
            market_charge.forEach(item => {
                result2 += item.cost
            })
            res.send({
                result1,
                result2,
            })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_charges', async (req, res, next) => {
        try {
            const chargeAmount = await db.getStats_chargesAmount({ from: req.query.from, to: req.query.to })
            const chargeSum = await db.getStats_chargesSum({ from: req.query.from, to: req.query.to })
            let result1 = 0,
                result2 = 0
            chargeAmount.forEach(item => {
                result1 += +item.amount
            })
            chargeSum.forEach(item => {
                result2 += +item.sum
            })
            res.send({
                result1,
                result2,
            })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_pass', async (req, res, next) => {
        try {
            const pass = await db.getStats_pass({ from: req.query.from, to: req.query.to })
            let amount = [],
                totalSum = 0
            const day = []
            pass.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_passSum', async (req, res, next) => {
        try {
            const passSum = await db.getStats_passSum({ from: req.query.from, to: req.query.to })
            let sum = [],
                totalSum = 0
            const day = []
            passSum.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                sum.push(item.sum)
                totalSum += +item.sum
            })
            res.send({ day, sum, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_checkOuts', async (req, res, next) => {
        try {
            const checkOuts = await db.getStats_checkOuts({ from: req.query.from, to: req.query.to })
            let amount = [],
                totalSum = 0
            const day = []
            checkOuts.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                amount.push(item.amount)
                totalSum += +item.amount
            })
            res.send({ day, amount, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_chargesSum', async (req, res, next) => {
        try {
            const chargeSum = await db.getStats_chargesSum({ from: req.query.from, to: req.query.to })
            let sum = [],
                totalSum = 0
            const day = []
            chargeSum.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                sum.push(item.sum)
                totalSum += +item.sum
            })
            res.send({ day, sum, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.get('/Stats_achievements', async (req, res, next) => {
        try {
            const achievements = await db.getStats_achievements({ from: req.query.from, to: req.query.to })
            let sum = [],
                totalSum = 0
            const day = []
            achievements.forEach(item => {
                const time = item.time.split('-')
                day.push(`${time[time.length - 1]}号`)
                sum.push(item.sum)
                totalSum += +item.sum
            })
            res.send({ day, sum, totalSum })
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_notPasss', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_notPasss({ from: req.body.from, to: req.body.to }))
            const resoult = []
            doc.forEach((item, i) => {
                resoult.push({
                    value: item.amount,
                    name: item.name
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_trials', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_trials({ from: req.body.from, to: req.body.to }))
            const resoult = []
            doc.forEach((item, i) => {
                resoult.push({
                    value: item.amount,
                    name: item.name
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_distributions', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_distributions({ from: req.body.from, to: req.body.to }))
            const resoult = []
            doc.forEach((item, i) => {
                resoult.push({
                    value: item.amount,
                    name: item.name
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_reasonCancellations', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_cancellations({ from: req.body.from, to: req.body.to }))
            const resoult = []
            doc.forEach((item, i) => {
                resoult.push({
                    value: item.amount,
                    name: item.name
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    router.post('/Stats_reasonNotsigns', async (req, res, next) => {
        try {
            const doc = humps.camelizeKeys(await db.getStats_notSigns({ from: req.body.from, to: req.body.to }))
            const resoult = []
            doc.forEach((item, i) => {
                resoult.push({
                    value: item.amount,
                    name: item.name
                });
            });
            res.send(resoult)
        } catch (err) {
            next(err)
        }
    })

    return router
})();